In a heterogeneous environment the source and target database management systems (DBMS) can differ by several criteria, including release versions of the same database product, and by computer architectures hosting the source and target DBMS.
In general, data replication refers to capturing changes made to a source, such as a database or file, and transmitting them to a target where they are applied, thereby creating a copy of the source. The target can be used in workload balancing, such as offloading read-only SQL operations from the source database. The target can also become the new source in a disaster recovery solution if the source becomes unavailable during an unrecoverable computer hardware failure.
A multi-row SQL statement, such as UPDATE or DELETE, is one that affects many rows. Generally, a database management system (DBMS) may determine that executing the multi-row SQL statement is more efficient than the corresponding series of individual SQL statements. However in a heterogeneous replication environment, the target DBMS may not fully implement the syntax of a complex multi-row SQL statement, as implemented by the source DBMS. Even if the multi-row SQL statement is supported at the target DBMS, the multi-row SQL statement is not directly executable at the target DBMS if data transformation between the source and target DBMSs is required. Transformation refers to substituting values associated with the columns of the tables of the source DMS into the corresponding columns of the tables of the target DBMS. Consequently, the source DBMS constructs the corresponding series of individual SQL statements, which are transmitted to the target DBMS where they are subsequently applied. Generating multiple individual SQL statements impacts performance on the source DBMS since as each SQL statement is analyzed, CPU cycles are diverted from processing transactions to generating and transmitting the multiple SQL statements to the target DBMS. Additionally, data replication performed in this way can increase the risk of data loss when transmission of replication SQL statements to the target database fails to complete.